package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedList;

public class GraphDao {

	private String sqlSelectValues		= "SELECT value, datum, YEAR(datum) as date_year, MONTH(datum) AS date_month, DAYOFMONTH(datum) AS date_day, HOUR(datum) AS date_hour, MINUTE(datum) AS date_minute FROM information WHERE type = ? AND datum >= ?  AND datum <= ?  ORDER BY datum ASC";
    private String sqlGraphDelete 		= "DELETE FROM graph where date = ?";
	
	private Connection        con      			= null ;
	private PreparedStatement psSelectValues   	= null ;
	private PreparedStatement psDelete 			= null ;
	
	public GraphDao() {
		DBmanager myDb = DBmanager.getInstance();
		con = myDb.getConnection();
		try {			
		this.psSelectValues 	 	= con.prepareStatement(sqlSelectValues);
	    this.psDelete 				= con.prepareStatement(sqlGraphDelete);			
		} catch(SQLException se) {
			printSQLException(se);
		}
	}

	private LinkedList<GraphBean> readData(String type) {
		LinkedList<GraphBean> graphValues = new LinkedList<GraphBean>();
		try {
			psSelectValues.clearParameters();

			psSelectValues.setString(1, type);
			
			ResultSet rs = psSelectValues.executeQuery();
			
			int count = 0;
			while (rs.next()) {
				Date date = rs.getDate("DATE");
				int value = rs.getInt("value");
				int value2 = rs.getInt("value2");
				GraphBean gb = new GraphBean(); 
				gb.setAll(date, value, value2) ;
				graphValues.add(gb);
			}
			rs.close();
		    
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return graphValues;
	}
	
	private void deleteData(String date) {
		try {
			psDelete.clearParameters();

			//param1
		    psDelete.setString(1, date);

		    int resultaat = psDelete.executeUpdate();
		    if (resultaat == 0) { System.out.println("nix done"); }
		    
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void printSQLException(SQLException se) {
		while(se != null) {

			System.out.print("SQLException: State:   " + se.getSQLState());
			System.out.println("Severity: " + se.getErrorCode());
			System.out.println(se.getMessage());			

			se = se.getNextException();
		}
	}
	
	public ResultSet getValues(int type, String startDate, String endDate) {
		try {
			System.err.println(startDate + "-" + endDate);
			psSelectValues.clearParameters();
			psSelectValues.setInt(1, type);
			psSelectValues.setString(2, startDate);
			psSelectValues.setString(3, endDate);
			System.err.println(psSelectValues.toString());
			return psSelectValues.executeQuery();
		} catch(SQLException se) {
			
			printSQLException(se) ;
		}
		return null;
	}

}
